﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Web;

namespace DataAccesslayer
{
    class ActiveDatabase
    {
        private SqlConnection conn = null;
        public ActiveDatabase()
        {
            conn = new SqlConnection(getConnectionString());
        }
        /// <summary>
        /// Get connecion string in file web.config
        /// </summary>
        /// <returns>An String</returns>
        private string getConnectionString()
        {
            string connstring = "";
            return connstring;
        }
        /// <summary>
        /// Create a object SqlParameter with paramName and value
        /// </summary>
        /// <param name="paramName">is parameter name</param>
        /// <param name="value">is value</param>
        /// <returns></returns>
        protected SqlParameter createPrameter(string paramName, object value)
        {
            SqlParameter param = new SqlParameter(paramName, value);
            return param;
        }
        /// <summary>
        /// function execute
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="procParams"></param>
        /// <returns>return a int value if the function execute success</returns>
        protected int executeNonQuery(string procName, params IDataParameter[] procParams)
        {
            SqlCommand cmd = null;
            int count;
            try
            {
                conn = new SqlConnection(getConnectionString());
                conn.Open();
                cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = procName;
                cmd.CommandType = CommandType.StoredProcedure;
                if (procParams != null)
                {
                    for (int i = 0; i < procParams.Length; i++)
                    {
                        cmd.Parameters.Add(procParams[i]);
                    }
                }
                count = cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                count = 0;
                //throw;
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }
                conn.Close();
            }
            return count;
        }
        /// <summary>
        /// function execute
        /// </summary>
        /// <param name="spName"></param>
        /// <param name="paramList"></param>
        /// <returns>return a dataset with the parameters in route</returns>
        protected DataSet executeDataSet(string spName, params IDataParameter[] paramList)
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;
            DataSet ds = new DataSet();
            SqlDataAdapter adapter = null;
            try
            {
                conn = new SqlConnection(getConnectionString());
                conn.Open();
                cmd = new SqlCommand(spName, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                if (paramList != null)
                {
                    for (int i = 0; i < paramList.Length; i++)
                    {
                        cmd.Parameters.Add(paramList[i]);
                    }
                }
                adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds);
            }
            catch (Exception)
            {
                return null;
                //throw;
            }
            finally
            {
                if (adapter != null)
                {
                    adapter.Dispose();
                }
                conn.Close();
            }
            return ds;
        }
        /// <summary>
        /// Function execute
        /// </summary>
        /// <param name="spName"></param>
        /// <param name="paramList"></param>
        /// <returns>return a datatable with the parameters in route</returns>
        protected DataTable executeDataTable(string spName, params IDataParameter[] paramList)
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;
            DataTable dt = new DataTable();
            SqlDataAdapter adapter = null;
            try
            {
                conn = new SqlConnection(getConnectionString());
                conn.Open();
                cmd = new SqlCommand(spName, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                if (paramList != null)
                {
                    for (int i = 0; i < paramList.Length; i++)
                    {
                        cmd.Parameters.Add(paramList[i]);
                    }
                }
                adapter = new SqlDataAdapter(cmd);
                adapter.Fill(dt);
            }
            catch (Exception)
            {
                return null;
                //throw;
            }
            finally
            {
                if (adapter != null)
                {
                    adapter.Dispose();
                }
                conn.Close();
            }
            return dt;
        }
    }
}
